﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;

namespace APMABusinessLogicCoreLib.Helpers
{
    public class DBHelper : IDisposable
    {
        #region Attributes

        private const int CommandTimeout = 1200;

        private const string TransactionName = "DBHelperTransaction";
        private SqlTransaction _transaction;
        private readonly SqlConnection _connection;

        public static string ConnectionString;

        static DBHelper()
        {
            //var settings = ConfigurationManager.ConnectionStrings["DAConfiguration"];
            //if (settings != null)
            //{
            //    ConnectionString = settings.ConnectionString;
            //}
        }

        #endregion Attributes

        #region Constructors

        public DBHelper()
            : this(ConnectionString)
        {
        }

        protected DBHelper(string connectionString)
        {
            if (string.IsNullOrEmpty(connectionString))
                throw new Exception("ErrConnectionStringIsEmpty");
            _connection = new SqlConnection(connectionString);
        }

        #endregion Constructors

        #region Properties

        public bool InTransaction
        {
            get { return _transaction != null; }
        }

        #endregion Properties

        #region Methods

        private void ConnectionOpenIfClose()
        {
            if (_connection.State != ConnectionState.Open)
            {
                try
                {
                    _connection.Open();
                }
                catch (Exception ex)
                {
                    throw new Exception(string.Format("ErrOpenConnection, {0}", ex));
                }
            }
        }

        public void BeginTransaction()
        {
            BeginTransaction(IsolationLevel.Unspecified);
        }

        public void BeginTransaction(IsolationLevel isolationLevel)
        {
            if (_transaction != null)
                throw new Exception("ErrBeginTransaction");

            ConnectionOpenIfClose();
            _transaction = _connection.BeginTransaction(isolationLevel, TransactionName);
        }

        public void CommitTransaction()
        {
            if (_transaction == null)
                throw new Exception("ErrCommitTransaction");

            _transaction.Commit();
            _transaction.Dispose();
            _transaction = null;
        }

        public void RollbackTransaction()
        {
            if (_transaction == null)
                throw new Exception("ErrRollbackTransaction");

            _transaction.Rollback(TransactionName);
            _transaction.Dispose();
            _transaction = null;
        }

        public void CloseConection()
        {
            if (_connection != null)
            {
                _connection.Close();
                _connection.Dispose();
            }
        }

        #region Executing methods

        public void SqlScriptExecuteFromFile(string sqlFile)
        {
            ConnectionOpenIfClose();

            SqlConnection.ClearAllPools();

            using (var reader = new StreamReader(sqlFile))
            {
                string commandText = string.Empty;
                do
                {
                    string str = reader.ReadLine();
                    if (str == null)
                        break;

                    str = str.Trim();

                    if (str == string.Empty)
                        continue;

                    if (str.ToLower() != "go")
                    {
                        commandText = string.Format("{0}\r\n {1}", commandText, str);
                    }
                    else
                    {
                        using (SqlCommand command = new SqlCommand())
                        {
                            command.CommandText = commandText;
                            command.Connection = _connection;
                            ExecuteCommandNonQuery(command);
                        }
                        commandText = string.Empty;
                    }
                } while (true);
            }
        }

        public int ExecStorProcedureInt(string storedProcName)
        {
            int ret = 0;

            try
            {
                using (var command = CreateCommandWithParameters((SqlParameter[])null))
                {
                    ret = AddReturnParamAndExecuteStoredProcedure(command, storedProcName, ret);
                }
            }
            catch (Exception ex)
            {
                //LogInstance.Log.Error("Exec Stor Procedure Int", ex);
                throw;
            }
            return ret;
        }

        public int ExecStorProcedureInt(string storedProcName, Dictionary<string, object> paramList)
        {
            int ret = 0;

            try
            {
                using (var command = CreateCommandWithParameters(paramList))
                {
                    ret = AddReturnParamAndExecuteStoredProcedure(command, storedProcName, ret);
                }
            }
            catch (Exception ex)
            {
                //LogInstance.Log.Error("Exec Stor Procedure Int", ex);
                throw;
            }
            return ret;
        }

        public int ExecStorProcedureInt(string storedProcName, List<SqlParameterWrapper> paramList)
        {
            int ret = 0;

            try
            {
                using (var command = CreateCommandWithParameters(paramList))
                {
                    ret = AddReturnParamAndExecuteStoredProcedure(command, storedProcName, ret);
                }
            }
            catch (Exception ex)
            {
                //LogInstance.Log.Error("Exec Stor Procedure Int", ex);
                throw;
            }
            return ret;
        }

        public int ExecStorProcedureInt(string storedProcName, SqlParameter[] paramList)
        {
            int ret = -1;

            try
            {
                using (SqlCommand command = CreateCommandWithParameters(paramList))
                {
                    ret = AddReturnParamAndExecuteStoredProcedure(command, storedProcName, ret);
                }
            }
            catch (Exception ex)
            {
                //LogInstance.Log.Error("Exec Stor Procedure Int", ex);
                throw;
            }
            return ret;
        }

        private static int AddReturnParamAndExecuteStoredProcedure(SqlCommand command,
            string storedProcName, int ret)
        {
            command.CommandText = storedProcName;

            var sqlReturnParam = new SqlParameter("ReturnValue", null);
            sqlReturnParam.Direction = ParameterDirection.ReturnValue;
            sqlReturnParam.Value = null;
            command.Parameters.Add(sqlReturnParam);

            ExecuteCommandNonQuery(command);

            if (command.Parameters["ReturnValue"] != null)
            {
                ret = Convert.ToInt32(command.Parameters["ReturnValue"].Value);
            }

            return ret;
        }

        public DataSet ExecStorProcedureDS(string storProcName)
        {
            return ExecStorProcedureDS(storProcName, null);
        }

        public DataSet ExecStorProcedureDS(string storProcName, Dictionary<string, object> paramList)
        {
            DataSet ds = new DataSet(storProcName);

            try
            {
                using (SqlCommand command = CreateCommandWithParameters(paramList))
                {
                    command.CommandText = storProcName;
                    FillDataSet(command, ds);
                }
            }
            catch (Exception ex)
            {
                //LogInstance.Log.Error("Exec Stor Procedure DS", ex);
                throw;
            }

            return ds;
        }

        public DataSet ExecuteSqlStatementForDataSet(string sqlStatement)
        {
            return ExecuteSqlStatementForDataSet(sqlStatement, null);
        }

        public DataSet ExecuteSqlStatementForDataSet(string sqlStatement, Dictionary<string, object> paramList)
        {
            DataSet ds = new DataSet();

            try
            {
                using (SqlCommand command = CreateCommandWithParameters(paramList))
                {
                    command.CommandText = sqlStatement;
                    command.CommandType = CommandType.Text;

                    FillDataSet(command, ds);
                }
            }
            catch (Exception ex)
            {
                //LogInstance.Log.Error("Execute Sql Statement For DataSet", ex);
                throw;
            }

            return ds;
        }

        public void ExecuteSqlStatement(string sqlStatement)
        {
            ExecuteSqlStatement(sqlStatement, null);
        }

        public void ExecuteSqlStatement(string sqlStatement, Dictionary<string, object> paramList)
        {
            try
            {
                using (SqlCommand command = CreateCommandWithParameters(paramList))
                {
                    command.CommandType = CommandType.Text;
                    command.CommandText = sqlStatement;
                    ExecuteCommandNonQuery(command);
                }
            }
            catch (Exception ex)
            {
                //LogInstance.Log.Error("Execute Sql Statement", ex);
                throw;
            }
        }

        private SqlCommand CreateCommandWithParameters(Dictionary<string, object> paramList)
        {
            SqlParameter[] sqlParameters = null;

            if (paramList != null)
            {
                sqlParameters = paramList.Select(p =>
                {
                    var sqlParam = new SqlParameter(p.Key, p.Value);
                    if (p.Value == null || p.Value.ToString().ToUpper() == "NULL")
                    {
                        sqlParam.IsNullable = true;
                        sqlParam.SqlValue = DBNull.Value;
                    }
                    return sqlParam;
                }).ToArray();
            }

            return CreateCommandWithParameters(sqlParameters);
        }

        private SqlCommand CreateCommandWithParameters(List<SqlParameterWrapper> paramList)
        {
            SqlParameter[] sqlParameters = null;

            if (paramList != null)
            {
                sqlParameters = paramList.ConvertAll(p => p.GetSqlParameter()).ToArray();
            }

            return CreateCommandWithParameters(sqlParameters);
        }

        private SqlCommand CreateCommandWithParameters(SqlParameter[] paramList)
        {
            ConnectionOpenIfClose();
            SqlCommand command = new SqlCommand();
            command.Transaction = _transaction;
            command.CommandTimeout = CommandTimeout;
            command.Connection = _connection;
            command.CommandType = CommandType.StoredProcedure;
            if (paramList != null)
                command.Parameters.AddRange(paramList);

            return command;
        }

        public bool IsExistName(string tableName, string columnName, string objectName)
        {
            return IsExistName(tableName, columnName, objectName, null);
        }

        public bool IsExistName(string tableName, string columnName, string objectName, Dictionary<string, object> paramList)
        {
            try
            {
                ConnectionOpenIfClose();

                using (var command = new SqlCommand())
                {
                    command.Connection = _connection;
                    command.Transaction = _transaction;

                    string sqlQuery = string.Format(" WHERE {0}='{1}' ", columnName, objectName);
                    if (paramList != null && paramList.Count != 0)
                    {
                        foreach (var p in paramList)
                        {
                            sqlQuery = string.Format("{0} and [{1}]<>@{1}", sqlQuery, p.Key);
                            var sqlParam = new SqlParameter(p.Key, p.Value);
                            command.Parameters.Add(sqlParam);
                        }
                    }
                    sqlQuery = string.Format("SELECT COUNT(*) as COUNT FROM {0}{1}", tableName, sqlQuery);

                    command.CommandText = sqlQuery;

                    int count = ExecuteCommandScalar(command);
                    return count != 0;
                }
            }
            catch (Exception ex)
            {
                //LogInstance.Log.Error("Is Exist Name", ex);
                throw;
            }

        }

        public bool IsExistRow(string tableName, Dictionary<string, object> paramList)
        {
            return GetRowCount(tableName, paramList) != 0;
        }

        public int GetRowCount(string tableName, Dictionary<string, object> paramList)
        {
            if (null == paramList)
            {
                throw new ArgumentNullException("paramList");
            }

            string sqlQuery = "";
            try
            {
                ConnectionOpenIfClose();

                using (var command = new SqlCommand())
                {
                    command.Connection = _connection;
                    command.Transaction = _transaction;
                    if (paramList.Count != 0)
                    {
                        sqlQuery = " WHERE ";
                        foreach (var p in paramList)
                        {
                            sqlQuery = string.Format("{0} [{1}]=@{1} AND", sqlQuery, p.Key);
                            var sqlParam = new SqlParameter(p.Key, p.Value);
                            command.Parameters.Add(sqlParam);
                        }
                        sqlQuery = sqlQuery.Remove(sqlQuery.Length - 4);
                    }

                    sqlQuery = string.Format("SELECT COUNT(1) as COUNT FROM {0}{1}", tableName, sqlQuery);

                    command.CommandText = sqlQuery;

                    int count = ExecuteCommandScalar(command);
                    return count;
                }
            }
            catch (Exception ex)
            {
                //LogInstance.Log.Error("GetRowCount()", ex);
                throw;
            }
        }

        public bool IsLastRow(string tableName)
        {
            return IsLastRow(tableName, null);
        }

        public bool IsLastRow(string tableName, Dictionary<string, object> parameters)
        {
            try
            {
                ConnectionOpenIfClose();
                using (var command = new SqlCommand())
                {
                    command.Connection = _connection;
                    command.Transaction = _transaction;

                    string selectQuery = string.Format("SELECT COUNT(1) as COUNT FROM {0}", tableName);
                    string conditionQuery = null;
                    if (parameters != null && parameters.Count > 0)
                    {
                        var conditions = new List<string>();
                        foreach (var pair in parameters)
                        {
                            conditions.Add(string.Format("[{0}] = @{0}", pair.Key));
                            var parameter = new SqlParameter(pair.Key, pair.Value);
                            command.Parameters.Add(parameter);
                        }
                        conditionQuery = string.Format(" WHERE {0}", string.Join(" AND ", conditions.ToArray()));
                    }

                    command.CommandText = string.Format("{0}{1}", selectQuery, conditionQuery);

                    return ExecuteCommandScalar(command) == 1;
                }
            }
            catch (Exception ex)
            {
                //LogInstance.Log.Error("Is Last Row", ex);
                throw;
            }
        }

        public Dictionary<string, object> GetStringToObjectDictionary(Dictionary<string, string> paramList)
        {
            Dictionary<string, object> list = null;

            if (paramList != null)
            {
                list = paramList.ToDictionary(element => element.Key, element => (object)element.Value);
            }

            return list;
        }

        #endregion Executing methods

        #endregion Methods

        #region IDisposable Members

        public void Dispose()
        {
            if (InTransaction)
            {
                RollbackTransaction();
            }

            CloseConection();
        }

        #endregion  

        #region Sql operations

        private static void FillDataSet(SqlCommand command, DataSet ds)
        {
            var sqlAdapter = new SqlDataAdapter(command);
            try
            {
                sqlAdapter.Fill(ds);
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        private static void ExecuteCommandNonQuery(IDbCommand command)
        {
            try
            {
                command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        private static int ExecuteCommandScalar(IDbCommand command)
        {
            try
            {
                return (int)command.ExecuteScalar();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

        #endregion Sql operations
    }

    /// <summary>
    /// Is intended for situation where it's necessary to set SQL parameter type (SqlDbType) explicitly.
    /// See ExecStorProcedureInt(String storedProcName, List&lt;SqlParameterWrapper&gt; paramList) and 
    /// CreateCommandWithParameters(List&lt;SqlParameterWrapper&gt; paramList) methods.
    /// Maybe it will be necessary to add new overloaded methods (ExecStorProcedureDS etc.) with List&lt;SqlParameterWrapper&gt; argument.
    /// </summary>
    public class SqlParameterWrapper
    {
        private readonly SqlParameter _parameter;

        public SqlParameterWrapper(string name, object paramValue, SqlDbType? type)
        {
            if (string.IsNullOrEmpty(name))
            {
                throw new ArgumentException("argument 'name' is null or empty");
            }

            _parameter = new SqlParameter();
            _parameter.ParameterName = name;
            if (null == paramValue || paramValue.ToString().ToUpper() == "NULL")
            {
                _parameter.Value = DBNull.Value;
            }
            else
            {
                _parameter.Value = paramValue;
            }

            if (type.HasValue)
            {
                _parameter.SqlDbType = type.Value;
            }
        }

        public SqlParameter GetSqlParameter()
        {
            return _parameter;
        }
    }
}
